﻿
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using DistributeAPI.Model;
using NHibernateVertica;
using george.Data;
using System.Data;

namespace DistributeAPI
{
    /// <summary>
    /// 企划订单量API
    /// </summary>
    public class PlanOrderQuantityHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 增加

        [JsonType]
        [MustLogin]
        [Description("新增")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult Create([ParamDescription("输入参数")]PlanOrderQuantityModelModify param)
        {
            if (string.IsNullOrWhiteSpace(param.ShopID)) return Error("店铺内码不能为空");
            if (string.IsNullOrWhiteSpace(param.ShopCode)) return Error("店铺编号不能为空");
            if (string.IsNullOrWhiteSpace(param.MaterialID)) return Error("货号不能为空");
            if (string.IsNullOrWhiteSpace(param.SizeID)) return Error("尺码内码不能为空");
            if (param.Qty < 0 || param.Qty > 999) return Error("数量的范围必须在0-999之间");
            if (AlreadyExists(param.ShopID, param.MaterialID, param.SizeID))
            {
                return Error("已存在当前货号和尺码");
            }


            try
            {
                string insertSql = $@"
                INSERT
	                INTO
		                goods.goods_DC_Bas_PLOrder(
			                CompanyID,
			                ShopID,
			                ShopCode,
			                OldShopCode,
			                MaterialID,
			                SizeID,
			                Qty,
			                MofifyDTM
		                )
	                VALUES(
		                '{param.CompanyID}',
		                '{param.ShopID}',
		                '{param.ShopCode}',
		                '{param.OldShopCode}',
		                '{param.MaterialID}',
		                '{param.SizeID}',
		                '{param.Qty}',
		                '{param.MofifyDTM}'
	                )
                ";
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = insertSql };
                cmdList.Add(cmd);
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
            return Success(0);
        }

        public bool AlreadyExists(string shopID, string materialID, string sizeID)
        {
            bool result = false;
            try
            {
                string strSql = $@"
                            SELECT COUNT(CompanyID) as COUNT
                            FROM goods.goods_DC_Bas_PLOrder
                            WHERE ShopID='{shopID}' and MaterialID='{materialID}' and SizeID='{sizeID}'
            ";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                if (count > 0)
                {
                    result = true;
                }
            }
            catch (Exception e)
            {
                throw;
            }

            return result;
        }

        #endregion 增加

        #region 删除

        [MustLogin]
        [Description("获取企划订单量列表")]
        [ReturnType(typeof(IList<PlanOrderQuantityModel>))]
        public ApiResult Remove([ParamDescription("店铺内码")] string shopID,
           [ParamDescription("货号")] string materialID,
           [ParamDescription("尺码内码")] string sizeID)
        {

            try
            {
                string deleteSql = $@"
                                    Delete
                                    FROM goods.goods_DC_Bas_PLOrder
                                    where ShopID='{shopID}' and MaterialID='{materialID}' and SizeID='{sizeID}'
                ";
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = deleteSql };
                cmdList.Add(cmd);
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception e)
            {
                throw;
            }
        }



        #endregion 删除

        #region 修改

        [JsonType]
        [MustLogin]
        [Description("新增")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult Modify([ParamDescription("输入参数")]PlanOrderQuantityModelModify param)
        {
            if (string.IsNullOrWhiteSpace(param.ShopID)) return Error("店铺内码不能为空");
            if (string.IsNullOrWhiteSpace(param.ShopCode)) return Error("店铺编号不能为空");
            if (string.IsNullOrWhiteSpace(param.MaterialID)) return Error("货号不能为空");
            if (string.IsNullOrWhiteSpace(param.SizeID)) return Error("尺码内码不能为空");
            if (param.Qty < 0 || param.Qty > 999) return Error("数量的范围必须在0-999之间");

            try
            {
                string insertSql = $@"
                Update
	                
		                goods.goods_DC_Bas_PLOrder
                Set
			                ShopID='{param.ShopID}',
			                ShopCode='{param.ShopCode}',
			                OldShopCode= '{param.OldShopCode}',
			                MaterialID='{param.MaterialID}',
			                SizeID= '{param.SizeID}',
			                Qty='{param.Qty}',
			                MofifyDTM='{param.MofifyDTM}'               
	            Where
                            ShopID='{param.OldShopID}' and
                            MaterialID='{param.OldMaterialID}' and 
                            SizeID= '{param.OldSizeID}'
                ";
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = insertSql };
                cmdList.Add(cmd);
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
            return Success(0);
        }


        [JsonType]
        [MustLogin]
        [Description("保存")]
        [ReturnType(typeof(PlanOrderQuantityModelModify))]
        public ApiResult Save([ParamDescription("输入参数")] PlanOrderQuantityModelSave param)
        {
            foreach (var item in param.List)
            {
                if (string.IsNullOrWhiteSpace(item.ShopID)) return Error("店铺内码不能为空");
                if (string.IsNullOrWhiteSpace(item.MaterialID)) return Error("货号不能为空");
                if (string.IsNullOrWhiteSpace(item.SizeID)) return Error("尺码内码不能为空");
                if (item.Qty < 0 || item.Qty > 999) return Error("数量的范围必须在0-999之间");
            }

            try
            {
                string saveSql = @"
                Update
	                
		                goods.goods_DC_Bas_PLOrder
                Set			                
			                Qty='{0}',
			                MofifyDTM='{1}'               
	            Where
                            ShopID='{2}' and
                            MaterialID='{3}' and 
                            SizeID= '{4}'
                ";
                if (param == null) return Error("参数对象为空");
                if (param.List != null)
                {
                    var cmdList = new List<CommandInfo>();
                    foreach (var item in param.List)
                    {
                        var cmd = new CommandInfo()
                        {
                            CommandText =
                                string.Format(saveSql,
                                              item.Qty,
                                              item.MofifyDTM,
                                              item.ShopID,
                                              item.MaterialID,
                                              item.SizeID),
                        };
                        cmdList.Add(cmd);
                    }
                    Helper.ExecuteSqlTran(cmdList);
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion 修改

        #region 查询
        [MustLogin]
        [Description("获取企划订单量列表")]
        [ReturnType(typeof(IList<PlanOrderQuantityModel>))]
        public ApiResult GetList(
            [ParamDescription("店铺名称或店铺编码")] string keys = "",
            [ParamDescription("货号")] string materialId = "",
            [ParamDescription("页码")] int Size = 1,
            [ParamDescription("页数")] int Page = 100)
        {
            var count = ReturnCount(keys, materialId);
            var result = PaperData<PlanOrderQuantityModel>.Create(count, Size, Page);
            string strSql = $@"
        Select o.CompanyID, o.ShopID, o.ShopCode, o.OldShopCode, o.MaterialID, o.SizeID, o.Qty,s.ShopName,ms.SizeName
        FROM goods.goods_DC_Bas_PLOrder o
        left join dw.dw_Shop s
        on o.ShopID = s.ShopID
        left join ods.ods_erp_SD_Mat_Size ms
        on o.sizeid = ms.sizeid
        where 1=1

        {(string.IsNullOrWhiteSpace(keys) ? "" : $" and( o.ShopCode ilike '%{keys}%' or s.ShopName ilike '%{keys}%')")}
        {(string.IsNullOrWhiteSpace(materialId) ? "" : $" and o.materialId ilike '%{materialId}%' ")}

        ORDER by o.CompanyID,o.ShopID,o.MaterialID,o.SizeID
        LIMIT {Size} offset {(Page - 1.0) * Size}	
        ";
            var cmd = Helper.GetSqlStringCommand(strSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<PlanOrderQuantityModel>.ConvertToModel(dt);
            result.Items = list;
            return Success(result);
        }

        [MustLogin]
        [Description("获取企划订单量列表")]
        [ReturnType(typeof(IList<PlanOrderQuantityModel>))]
        public ApiResult GetDetails([ParamDescription("店铺内码")] string shopID,
           [ParamDescription("货号")] string materialID,
           [ParamDescription("尺码内码")] string sizeID)
        {

            try
            {
                string strSql = $@"
        Select o.CompanyID, o.ShopID, o.ShopCode, o.OldShopCode, o.MaterialID, o.SizeID, o.Qty,s.ShopName,ms.SizeName,ms.SizeCodeType
        FROM goods.goods_DC_Bas_PLOrder o
        left join dw.dw_Shop s
        on o.ShopID = s.ShopID
        left join ods.ods_erp_SD_Mat_Size ms
        on o.sizeid = ms.sizeid
        where o.ShopID='{shopID}' and o.MaterialID='{materialID}' and o.SizeID='{sizeID}'
                ";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PlanOrderQuantityModel>.ConvertToModel(dt);
                return Success(list.FirstOrDefault());
            }
            catch (Exception e)
            {
                throw;
            }
        }

        #region 返回行数


        public int ReturnCount(
            [ParamDescription("店铺名称或店铺编码")] string keys = "",
            [ParamDescription("货号")] string materialId = "")
        {
            try
            {
                var countSql = string.Empty;
                #region 查询时获取行数

                countSql = $@"

        select count(1) as count 
        FROM goods.goods_DC_Bas_PLOrder o
        left join dw.dw_Shop s
        on o.ShopID = s.ShopID
        left join ods.ods_erp_SD_Mat_Size ms
        on o.sizeid = ms.sizeid
        where 1=1

        {(string.IsNullOrWhiteSpace(keys) ? "" : $" and( o.ShopCode ilike '%{keys}%' or s.ShopName ilike '%{keys}%')")}
        {(string.IsNullOrWhiteSpace(materialId) ? "" : $" and o.materialId ilike '%{materialId}%' ")}

";
                #endregion 查询时获取行数

                var cmd = Helper.GetSqlStringCommand(countSql);

                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion 返回行数

        #endregion 查询

        #region Excel导入

        /// <summary>
        /// 插入数据到临时表
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-07-14
        public void CreateTempTable(DataTable dt, string TempTableName)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    CompanyID varchar(30),
    ShopCode varchar(30),
    ShopName varchar(60),
    MaterialID varchar(45),
    TotalQty Integer,
    SizeLength varchar(15)
) ";
                Helper.ExecuteSqlTran(dt, TempTableName, sql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-05-27
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion Excel导入
    }
}
